// https://www.yuque.com/yashujs/bfug6u/mv8e02
// DDL(Data Definition Language)数据定义语言
const TableName = "IMGDATA";
// 表名: IMGDATA
// 字段1 id 整数 主键 自增
// 字段2 name 字符串 不可为空
// 字段3 img BLOB
let db = sqlite.open(
  "./data.db",
  { version: 1 },
  {
    onOpen: function (db) {
      db.execSQL(
        "CREATE TABLE IF NOT EXISTS " +
          TableName +
          "(" +
          "`id` INTEGER PRIMARY KEY AUTOINCREMENT, " +
          "`name` TEXT NOT NULL UNIQUE, " +
          "`img` BLOB" +
          ")"
      );
    },
  }
);

// 打印所有表
let db = sqlite.open("./data.db");
log("数据库所有表的信息: ", db.rawQuery("SELECT * FROM sqlite_master WHERE type='table'", null).all());

// 删除表
const TableName = "IMGDATA";
let db = sqlite.open(
  "./data.db",
  { version: 1 },
  {
    onOpen: function (db) {
      db.execSQL("drop TABLE " + TableName);
    },
  }
);

// DML
// 增加
let img = images.read(files.join(files.getSdcardPath(), "test.jpg"));
let r = db.insert(TableName, {
  name: "test",
  img: img,
});
log(r);

// 删除

log("删除name的值为test的记录: ", db.delete(TableName, "name = 'test'", null));

// 修改
// 修改符合条件name=test的记录, 字段name的改为测试
log("修改: ", db.update(TableName, { name: "测试" }, "name = ?", ["test"]));
log("修改后: ", db.rawQuery("SELECT * FROM " + TableName + " WHERE name = ?", ["测试"]).single());

// 查询
log("所有数据: ", db.rawQuery("SELECT * FROM " + TableName, null).all());
log("第一个数据: ", db.rawQuery("SELECT * FROM " + TableName, null).single());

// 条件查询
log("条件查询: ", db.rawQuery("SELECT * FROM " + TableName + " WHERE name = ?", ["测试"]).single());

// 事务
const TableName = "IMGDATA";
let db = sqlite.open("./data.db");
​
db.transaction(function () {
  db.update(TableName, { name: "测试改名字" }, "name = '测试'", null);
})
  .on("begin", function (t) {
    log("事务开始: ", t);
  })
  .on("commit", function (t) {
    log("事务完成: ", t);
  })
  .on("rollback", function (t) {
    log("事务回滚: ", t);
  })
  .on("end", function (t) {
    log("事务结束: ", t);
  })
  .on("error", function (e) {
    console.error(e);
  });

// ​关闭数据库
db.close();